﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Drawing.Imaging;
using System.Drawing;

namespace Moose.DBUtility
{
    public class DataBaseOp
    {
        private string SqlConnectionString;    //数据库连接

        private SqlConnection SqlConnection;
        /// <summary>
        /// 构造函数
        /// 初始化连接数据库参数
        /// </summary>
        private DataBaseOp()
        {
            SqlConnectionString = "Data Source=.;Initial Catalog=DataBaseName;User ID=sa;pwd=123456;Connection Lifetime=0;max pool size=200";
        }

        /// <summary>
        /// 构造函数
        /// 初始化连接数据库参数
        /// </summary>
        /// <param name="ConSqlServer">连接对象</param>
        public DataBaseOp(string ConSqlServer)
        {
            SqlConnectionString = ConSqlServer;
        }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        private string GetConnstr()
        {
            return SqlConnectionString;
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="cn">连接</param>
        public void Open()
        {
            try
            {
                if (SqlConnection != null && SqlConnection.State != ConnectionState.Closed)
                {
                    SqlConnection.Close();
                    SqlConnection.Dispose();
                }

                SqlConnection = new SqlConnection(GetConnstr());
                SqlConnection.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("打开数据库失败，错误信息：" + ex.Message);
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            try
            {
                if (SqlConnection != null)
                {
                    if (SqlConnection.State == ConnectionState.Open)
                    {
                        SqlConnection.Close();
                    }
                    SqlConnection.Dispose();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("关闭数据库失败，错误信息：" + ex.Message);
            }
            
        }

        /// <summary>
        /// 查询 ExecuteReader.Read()
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <returns>是否存在</returns>
        public bool ExecuteReader(string sqlScript)
        {
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    return cmd.ExecuteReader().Read();
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 查询（SqlDataReader）
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <returns>查询结果</returns>
        public SqlDataReader GetDR(string sqlScript)
        {
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 查询（DataSet）
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <returns>查询结果</returns>
        public DataSet GetDS(string sqlScript)
        {
            SqlDataAdapter _Adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    _Adapter = new SqlDataAdapter(sqlScript, SqlConnection);
                    _Adapter.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 查询（DataSet）
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <param name="tableName">指定DataSet["tableName"]表</param>
        /// <returns>查询结果</returns>
        public DataSet GetDS(string sqlScript, string tableName)
        {
            SqlDataAdapter _Adapter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    _Adapter = new SqlDataAdapter(sqlScript, SqlConnection);
                    _Adapter.Fill(ds, tableName);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 查询（DataTable）
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <returns>查询结果</returns>
        public DataTable GetDT(string sqlScript)
        {
            return GetDS(sqlScript).Tables[0];
        }

        /// <summary>
        /// 查询（DataView）
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <returns>查询结果</returns>
        public DataView GetDV(string sqlScript)
        {
            return GetDS(sqlScript).Tables[0].DefaultView;
        }

        /// <summary>
        /// 增删改，无图片
        /// </summary>
        /// <param name="strSql">SQL语句</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQuery(string sqlScript)
        {
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 增改，有图片
        /// </summary>
        /// <param name="sqlScript">SQL语句</param>
        /// <param name="picbyte">图片的二进制数据</param>
        /// <returns>影响的行数</returns>
        public int ExecuteNonQuery(string sqlScript, byte[] picbyte)
        {
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                cmd.Parameters.Add("@Image", SqlDbType.Image);
                cmd.Parameters["@Image"].Value = picbyte;
                try
                {
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }
            }
        }

        /// <summary>
        /// 返回SQL语句执行结果的第一行第一列
        /// </summary>
        /// <param name="sqlScript"></param>
        /// <returns></returns>
        public string ExecuteScalar(string sqlScript)
        {
            string result = "";
            object obj = null;
            using (SqlCommand cmd = SqlConnection.CreateCommand())
            {
                cmd.CommandText = sqlScript;
                try
                {
                    obj = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    throw new Exception(sqlScript + ex.ToString());
                }

                if (obj == null || obj.Equals(null) || obj.Equals(System.DBNull.Value))
                {
                    result = "";
                }
                else
                {
                    result = obj.ToString();
                }
            }
            return result;
        }


        public static byte[] ImageToBytes(Image image)
        {
            MemoryStream ms = new MemoryStream();
            image.Save(ms, ImageFormat.Jpeg);
            byte[] bytes = new Byte[ms.Length];
            ms.Position = 0;
            ms.Read(bytes, 0, bytes.Length);
            ms.Close();
            return bytes;
        }

        // 将二进制序列转换为Image
        public static Image BytesToImage(byte[] bytes)
        {
            try
            {
                using (Stream fStream = new MemoryStream(bytes.Length))
                {
                    BinaryWriter bw = new BinaryWriter(fStream);
                    bw.Write(bytes);
                    bw.Flush();
                    Bitmap bitMap = new Bitmap(fStream);
                    bw.Close();
                    fStream.Close();
                    Image image = Image.FromHbitmap(bitMap.GetHbitmap());
                    return image;
                }
            }
            catch (IOException ex)
            {
                throw new Exception("读取图片失败：" + ex.Message);

            }
        }


    }
}
